/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Controlador;

import Modelo.Cotizacion;
import Modelo.Pedido;
import Modelo.Promocion;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;

/**
 *
 * @author Damian
 */
public class ControlCotizacion extends Conexion {
    
    public ControlCotizacion() {
    }
    
    public Integer insertar(Cotizacion cot) throws SQLException {
        Integer codigo=-1;
        Connection cn = conectar();
        
        if (cn.getAutoCommit()) {
            cn.setAutoCommit(false);
        }
        PreparedStatement ps = cn.prepareStatement("INSERT INTO "
                + "cotizacion("
                + " cli_historiadecliente,usu_cedula,"
                + " cot_fechagenerada,cot_fechafinal,"
                + " cot_estado,cot_formapago,"
                + " cot_observacion,cot_subtotal,cot_tiempoEntrega) "
                + "VALUES (?,?,?,?,?,?,?,?,?) ",Statement.RETURN_GENERATED_KEYS);
        ps.setInt(1, cot.getCli_historia());
        ps.setString(2, cot.getUsu_cedula());
        Date fg=null,ff=null;
        try {
            fg = new Date(cot.getFechaGenerada().getTime());
            ff = new Date(cot.getFechaFinal().getTime());
        } catch (Exception e) {
        }
        ps.setDate(3,fg );
        ps.setDate(4, ff);
        ps.setBoolean(5, cot.isEstado());
        ps.setString(6, cot.getFormaPago());
        ps.setString(7, cot.getObservacion());
        ps.setFloat(8, cot.getSubtotal());
        ps.setInt(9, cot.getTiempoEntrega());
        
        int a = ps.executeUpdate();
        ResultSet res=ps.getGeneratedKeys();
        while (res.next()) {            
            codigo = res.getInt(1);
        }
        
        //System.out.println("a:" + a);
        if (a >= 1) {
            cn.commit();
            System.out.println("Cotización "+codigo+" Registrada correctamente");
        }
        return codigo;
    }
    
    public void actualizar(Cotizacion cot) throws SQLException {
        Connection cn = conectar();
        
        if (cn.getAutoCommit()) {
            cn.setAutoCommit(false);
        }
        
        PreparedStatement ps = cn.prepareStatement("UPDATE  cotizacion SET  "
                + " cli_historiadecliente=?,"
                + " usu_cedula=?,cot_fechagenerada=?,"
                + " cot_fechafinal=?,cot_estado=?,"
                + " cot_formapago=?,cot_observacion=?,"
                + " cot_subtotal=?,cot_tiempoEntrega=? "
                + "WHERE cot_codigo=?");
        
        ps.setInt(1, cot.getCli_historia());
        ps.setString(2, cot.getUsu_cedula());
        ps.setDate(3, new Date(cot.getFechaGenerada().getTime()));
        ps.setDate(4, new Date(cot.getFechaFinal().getTime()));
        ps.setBoolean(5, cot.isEstado());
        ps.setString(6, cot.getFormaPago());
        ps.setString(7, cot.getObservacion());
        ps.setFloat(8, cot.getSubtotal());
        ps.setInt(9, cot.getTiempoEntrega());
        ps.setInt(10, cot.getCodigo());
        
        int a = ps.executeUpdate();
        
        if (a >= 1) {
            System.out.println("Cotización"+cot+" Actualizada Correctamente");
            cn.commit();
        }
    }
    
    public Cotizacion buscarCotizacion(int codigo) throws SQLException {
        Cotizacion prom = null;
        String sql = "SELECT * FROM cotizacion WHERE cot_codigo=?";
        Connection cn = conectar();
        PreparedStatement ps = cn.prepareStatement(sql);
        ps.setInt(1, codigo);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Cotizacion obj = new Cotizacion();
            obj.setCodigo(rs.getInt("cot_codigo"));
            obj.setCli_historia(rs.getInt("cli_historiadecliente"));
            obj.setUsu_cedula(rs.getString("usu_cedula"));
            obj.setFechaFinal(new java.util.Date(rs.getDate("cot_fechafinal").getTime()));
            obj.setFechaGenerada(new java.util.Date(rs.getDate("cot_fechagenerada").getTime()));
            obj.setEstado(rs.getBoolean("cot_estado"));
            obj.setFormaPago(rs.getString("cot_formapago"));
            obj.setObservacion(rs.getString("cot_observacion"));
            obj.setSubtotal(rs.getFloat("cot_subtotal"));
            obj.setTiempoEntrega(rs.getInt("cot_tiempoEntrega"));
            obj.setPedidos(listarPedidos(codigo));
            prom = obj;
        }
        desconectar(cn);

        //System.out.println(lista.toString());
        return prom;
        
    }
    
    public ArrayList<Pedido> listarPedidos(int cod) throws SQLException {
        ArrayList<Pedido> lista = null;
        String sql = "SELECT * FROM pedido WHERE cot_codigo=?";
        Connection cn = conectar();
        lista = new ArrayList<>();
        PreparedStatement ps = cn.prepareStatement(sql);
        ps.setInt(1, cod);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Pedido obj = new Pedido();
            obj.setCodigo(rs.getInt("ped_codigo"));
            String prod=rs.getString("prod_codigo");
            obj.setCod_prod(prod==null?-1:Integer.parseInt(prod));
            obj.setCod_cotizacion(rs.getInt("cot_codigo"));
            String servi=rs.getString("ser_codigo");
            obj.setCod_serv(servi==null?-1:Integer.parseInt(servi));
            obj.setPed_cantidad(rs.getInt("ped_cantidad"));
            lista.add(obj);
        }
        desconectar(cn);
        //System.out.println(lista.toString());
        return lista;
    }
    
    public void insertarPed(Pedido p) throws SQLException{
        Connection cn = conectar();
        
        if (cn.getAutoCommit()) {
            cn.setAutoCommit(false);
        }
        PreparedStatement ps = cn.prepareStatement("INSERT INTO "
                + "pedido("
                + " prod_codigo,cot_codigo,"
                + " ser_codigo,ped_cantidad) "
                + "VALUES (?,?,?,?) ");
        if (p.getCod_prod() != -1 ) {
            ps.setInt(1,  p.getCod_prod() );
        } else {
            ps.setNull(1, Types.INTEGER);
        }
        ps.setInt(2, p.getCod_cotizacion());
        if (p.getCod_serv() != -1 ) {
            ps.setInt(3,  p.getCod_serv() );
        } else {
            ps.setNull(3, Types.INTEGER);
        }
        
        ps.setInt(4, p.getPed_cantidad());
        
        int a = ps.executeUpdate();
        //System.out.println("a:" + a);
        if (a >= 1) {
            cn.commit();
            System.out.println("Pedido Registrado correctamente");
        }
    }
    public ArrayList<Cotizacion> listar() throws SQLException {
        ArrayList<Cotizacion> lista = null;
        String sql = "SELECT * FROM cotizacion";
        Connection cn = conectar();
        lista = new ArrayList<>();
        PreparedStatement ps = cn.prepareStatement(sql);
        //ps.setInt(1, cod);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Cotizacion obj = new Cotizacion();
            obj.setCodigo(rs.getInt("cot_codigo"));
            obj.setCli_historia(rs.getInt("cli_historiadecliente"));
            obj.setUsu_cedula(rs.getString("usu_cedula"));
            try {
                obj.setFechaFinal(new java.util.Date(rs.getDate("cot_fechafinal").getTime()));
                
            } catch (NullPointerException nullPointerException) {
                
            }
            try {
                obj.setFechaGenerada(new java.util.Date(rs.getDate("cot_fechagenerada").getTime()));
            } catch (NullPointerException nullPointerException) {
            }
            obj.setEstado(rs.getBoolean("cot_estado"));
            obj.setFormaPago(rs.getString("cot_formapago"));
            obj.setObservacion(rs.getString("cot_observacion"));
            obj.setSubtotal(rs.getFloat("cot_subtotal"));
            obj.setTiempoEntrega(rs.getInt("cot_tiempoEntrega"));
            obj.setPedidos(listarPedidos(obj.getCodigo()));
            lista.add(obj);
        }
        desconectar(cn);
        //System.out.println(lista.toString());
        return lista;
    }
    public void eliminarPedidos(Integer codCot) throws SQLException {
        Connection cn = conectar();
        PreparedStatement ps = cn.prepareStatement("DELETE FROM pedido WHERE cot_codigo=?");
        ps.setInt(1, codCot);
        int num = ps.executeUpdate();
        if (num != 0) {
            //JOptionPane.showMessageDialog(null, "eliminado");
            System.out.println("Pedidos antiguos removidos "+num+" de la cot "+codCot);
        }
    }
}
